﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Policy
/// </summary>
public class Policy
{
	public Policy(){}

    public int userId { set; get; }
    public string policyNo { set; get; }
    public string nric { set; get; } 
    public int accountId { set; get; }
    public DateTime issueDate { set; get; }
    public string benefitType { set; get; }
    public int staffId { set; get; }
    public decimal basicSumInsured { set; get; }
    public decimal plusRiderSumInsured { set; get; }
    public decimal waiverRiderPremium { set; get; }
    public decimal totalAnnualPremium { set; get; }
    public string premiumFrequency { set; get; }
    public string paymentMethod { set; get; }
    public string status { set; get; }

    //Set all atributes based on policy no.retrieved from database(R_POLICY) to the new object
    public void load(string policyNo)
    {
        Policy policy = Policy.getPolicy(policyNo);
        this.policyNo = policy.policyNo;
        this.nric = policy.nric;
        this.accountId = policy.accountId;
        this.userId = policy.userId;
        this.issueDate = policy.issueDate;
        this.benefitType = policy.benefitType;
        this.staffId = policy.staffId;
        this.basicSumInsured = policy.basicSumInsured;
        this.plusRiderSumInsured = policy.plusRiderSumInsured;
        this.waiverRiderPremium = policy.waiverRiderPremium;
        this.totalAnnualPremium = policy.totalAnnualPremium;
        this.premiumFrequency = policy.premiumFrequency;
        this.paymentMethod = policy.paymentMethod;
        this.status = policy.status;
    }

    //Retrieve data from R_POLICY
    public static Policy getPolicy(string policyNo)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT policy_no,P.user_id,issue_date, benefit_type, staff_id, basic_sum_insured, plus_rider_sum_insured, waiver_rider_premium, total_annual_premium, premium_frequency, payment_method, status, accountID,C.nric " +
                      " FROM R_POLICY P, CUSTOMER C" +
                      " WHERE P.user_id=C.userId and P.POLICY_NO=@policyNo";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@policyNo", (object)policyNo ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();
        Policy policy = new Policy();

        
        if (dr.Read())
        {
            policy.policyNo = dr.GetString(0);
            policy.userId = dr.GetInt32(1);
            policy.issueDate = dr.GetDateTime(2);
            policy.benefitType = dr.GetString(3);
            policy.staffId = dr.GetInt32(4);
            policy.basicSumInsured = dr.GetDecimal(5);
            policy.plusRiderSumInsured = dr.GetDecimal(6);
            policy.waiverRiderPremium = dr.GetDecimal(7);
            policy.totalAnnualPremium = dr.GetDecimal(8);
            policy.premiumFrequency = dr.GetString(9);
            policy.paymentMethod = dr.GetString(10);
            policy.status = dr.GetString(11);
            policy.accountId = dr.GetInt32(12);
            policy.nric = dr.GetString(13);
        }

        dr.Close();
        conn.Close();

        return policy;
    }

    public  decimal checkAccountValue(DateTime sellDate)
    {
        List<Fund> funds = Fund.getActivePortfolioFunds(this.policyNo);
        decimal accountValue = 0;
        foreach(Fund fund in funds){
            FundPrice presentPrice = fund.findFundPrice(sellDate);
            accountValue = accountValue + fund.investUnits * presentPrice.bidPrice;
           
        }
        return accountValue;
    }

    public static List<Policy> getPolicy(int custId)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT policy_no, P.user_id,issue_date, benefit_type, staff_id, basic_sum_insured, plus_rider_sum_insured, waiver_rider_premium, total_annual_premium, premium_frequency, payment_method, status, accountID,C.nric " +
                      " FROM R_POLICY P, CUSTOMER C" +
                      " WHERE P.user_id=C.userId and C.userId="+custId +" order by policy_no desc";

        SqlCommand cmd = new SqlCommand(sql, conn);

        SqlDataReader dr = cmd.ExecuteReader();

        List<Policy> policies = new List<Policy>();

        while(dr.Read())
        {
            Policy policy = new Policy();

            policy.policyNo = dr.GetString(0);
            policy.userId = dr.GetInt32(1);
            policy.issueDate = dr.GetDateTime(2);
            policy.benefitType = dr.GetString(3);
            policy.staffId = dr.GetInt32(4);
            policy.basicSumInsured = dr.GetDecimal(5);
            policy.plusRiderSumInsured = dr.GetDecimal(6);
            policy.waiverRiderPremium = dr.GetDecimal(7);
            policy.totalAnnualPremium = dr.GetDecimal(8);
            policy.premiumFrequency = dr.GetString(9);
            policy.paymentMethod = dr.GetString(10);
            policy.status = dr.GetString(11);
            policy.accountId = dr.GetInt32(12);
            policy.nric = dr.GetString(13);

            policies.Add(policy);
        }

        dr.Close();
        conn.Close();

        return policies;
    }


    public  void updatePolicy(string policyNo)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "UPDATE R_POLICY "
                 + "SET accountID=@accountId"
                 + ",basic_sum_insured=@basicSumInsured"
                 + ",plus_rider_sum_insured=@plusRiderSumInsured"
                 + ",waiver_rider_premium=@waiverRiderPremium"
                 + ",total_annual_premium=@totalAnnualPremium"
                 + ",premium_frequency=@premiumFrequency"
                 + ",payment_method=@paymentMethod"
                 + ",status=@status"
                 + " WHERE policy_no = '" + policyNo + "'";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.AddWithValue("@accountId", accountId);
        cmd.Parameters.AddWithValue("@basicSumInsured", basicSumInsured);
        cmd.Parameters.AddWithValue("@plusRiderSumInsured", plusRiderSumInsured);
        cmd.Parameters.AddWithValue("@waiverRiderPremium", waiverRiderPremium);
        cmd.Parameters.AddWithValue("@totalAnnualPremium", totalAnnualPremium);
        cmd.Parameters.AddWithValue("@premiumFrequency", premiumFrequency);
        cmd.Parameters.AddWithValue("@paymentMethod", paymentMethod);
        cmd.Parameters.AddWithValue("@status", status);
       
        cmd.ExecuteNonQuery();

        conn.Close();

    }
}